# -*- coding: utf-8 -*-
"""
PostgreSQL 数据库工具（兼容主项目接口）

@author: AI Assistant
"""
import psycopg2
from loguru import logger

import sys
from pathlib import Path
project_root = Path(__file__).resolve().parent.parent
if str(project_root) not in sys.path:
    sys.path.insert(0, str(project_root))

from config import WATER_DATABASE_URL


def get_postgres_data():
    """获取PostgreSQL连接（只读模式）
    
    Returns:
        psycopg2 连接对象
    """
    try:
        if not WATER_DATABASE_URL:
            raise ValueError("数据库URL未配置")
        
        conn = psycopg2.connect(WATER_DATABASE_URL)
        logger.debug("数据库连接成功")
        return conn
    except Exception as e:
        logger.error(f"数据库连接失败: {e}")
        raise


def get_data(sql: str, params: tuple = None):
    """执行查询并返回结果
    
    Args:
        sql: SQL 查询语句
        params: 查询参数
        
    Returns:
        查询结果列表
    """
    try:
        conn = get_postgres_data()
        cursor = conn.cursor()
        
        if params:
            cursor.execute(sql, params)
        else:
            cursor.execute(sql)
        
        results = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return results
        
    except Exception as e:
        logger.error(f"执行查询失败: {str(e)}")
        if 'conn' in locals():
            conn.close()
        raise


if __name__ == "__main__":
    # 测试数据库连接
    try:
        conn = get_postgres_data()
        logger.info("✅ 数据库连接成功")
        
        # 测试查询
        cursor = conn.cursor()
        cursor.execute("SELECT version();")
        version = cursor.fetchone()
        logger.info(f"PostgreSQL 版本: {version[0]}")
        
        cursor.close()
        conn.close()
    except Exception as e:
        logger.error(f"❌ 数据库测试失败: {e}")
